﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LotteryVote.Entity;
using System.Data;
using System.Data.SqlClient;
using LotteryVote.Utility;

namespace LotteryVote.Data
{
    public class UserDataAccess : DataBase
    {
        private readonly string _JoinUserInfoSql = @"select u.*,ui.*,(select top 1 ll.LastLoginTime from tb_LoginLog ll where ll.UserId=u.UserId order by ll.LoginId desc) as LastLoginTime from tb_User as u join tb_UserInfo as ui on ui.UserId=u.UserId";
        //用于分页的基础SQL
        private readonly string _JoinUserInfoWithPageSql = @"select u.ParentId,u.RoleId,u.UserName,ui.*,(select top 1 ll.LastLoginTime from tb_LoginLog ll where ll.UserId=u.UserId order by ll.LoginId desc) as LastLoginTime from tb_User as u join tb_UserInfo as ui on ui.UserId=u.UserId";

        public void Insert(User user)
        {
            string sql = string.Format(@"Insert into {0} ({1},{2},{3}) values(@{1},@{2},@{3}) SELECT IDENT_CURRENT('{0}')",
                User.TABLENAME, User.USERNAME, User.ROLEID, User.PARENTID);
            object id = base.ExecuteScalar(sql, new SqlParameter(User.USERNAME, user.UserName),
                new SqlParameter(User.ROLEID, user.RoleId), new SqlParameter(User.PARENTID, user.ParentId));
            user.UserId = Convert.ToInt32(id);
        }
        public void Delete(User user)
        {
            string sql = string.Format(@"DELETE {0} WHERE {1}=@{1}", User.TABLENAME, User.USERID);
            base.ExecuteNonQuery(sql, new SqlParameter(User.USERID, user.UserId));
        }

        public IList<User> GetAllUser()
        {
            string sql = @"select u.*,ui.* from tb_User as u
join tb_UserInfo as ui on ui.UserId=u.UserId";
            var userList = base.ExecuteList<User>(sql).ToList();
            var userInfoList = base.ToEnties<UserInfo>(base.DataTable);
            userList.ForEach(it => { it.UserCommission = base.ToEnties<UserCommission>(base.DataTable); it.UserInfo = userInfoList.Where(s => s.UserId == it.UserId).SingleOrDefault(); });
            return userList;
        }
        public IList<User> GetUserByRole(SystemRole role)
        {
            string sql = string.Format(@"{0} WHERE {1}=@{1}", _JoinUserInfoSql, User.ROLEID);
            var returnVal = base.ExecuteList<User>(sql, new SqlParameter(User.ROLEID, (int)role));
            returnVal.ForEach(it => it.UserCommission = base.ToEnties<UserCommission>(this.DataTable));
            return returnVal;
        }
        /// <summary>
        /// 获取用户的祖先用户.
        /// </summary>
        /// <param name="user">The user.</param>
        /// <returns></returns>
        public IList<User> GetParentUser(User user)
        {
            string sql = string.Format(@";WITH CTE AS
(
SELECT * FROM {0}  WHERE {1}=@{1}
UNION ALL
SELECT B.* FROM {0} AS B,CTE AS C WHERE B.{1}=C.{2} and c.{1}>b.{1}
)
SELECT * FROM CTE
 order by UserId", User.TABLENAME, User.USERID, User.PARENTID);
            return base.ExecuteList<User>(sql, new SqlParameter(User.USERID, user.UserId));
        }
        public IList<User> GetDescendantUser(User user)
        {
            return GetDescendantByContidion(user, string.Empty);
        }
        public IList<User> GetDescendantByRole(User user, SystemRole role)
        {
            string condition = string.Format("u.{0}=@{0}", User.ROLEID);
            return GetDescendantByContidion(user, condition, new SqlParameter(User.ROLEID, (int)role));
        }
        public IList<User> GetDescendantByContidion(User user, string condition, params SqlParameter[] cmdParams)
        {
            string sql = string.Format(@";WITH CTE AS
(
SELECT * FROM {1}  WHERE {2}=@{2}
UNION ALL
SELECT B.* FROM {1} AS B,CTE AS C WHERE B.{3}=C.{2} and B.{2}>C.{2}
)
select u.*,ui.* from CTE as u join tb_UserInfo as ui on ui.UserId=u.UserId
 {0}", string.IsNullOrEmpty(condition) ? string.Empty : " where " + condition,
                      User.TABLENAME, User.USERID, User.PARENTID);

            SqlParameter[] args = new SqlParameter[cmdParams.Length + 1];
            args[0] = new SqlParameter(User.USERID, user.UserId);
            for (int i = 0; i < cmdParams.Length; i++)
                args[i + 1] = cmdParams[i];

            return base.ExecuteList<User>(sql, args);
        }
        public IList<User> GetChild(User user)
        {
            string sql = string.Format(@"{0} WHERE u.{1}=@{1}", _JoinUserInfoSql, User.PARENTID);
            return base.ExecuteList<User>(sql, new SqlParameter(User.PARENTID, user.UserId));
        }
        public User GetUserById(int userId)
        {
            string sql = string.Format(@"{0} where u.UserId=@{1}", _JoinUserInfoSql, User.USERID);
            var user = base.ExecuteEntity<User>(sql, new SqlParameter(User.USERID, userId));
            LoadUserInfo(user);
            return user;
        }
        public User GetUserByUserName(string userName)
        {
            string sql = string.Format(@"{0} where u.{1}=@{1}", _JoinUserInfoSql, User.USERNAME);
            var user = base.ExecuteEntity<User>(sql, new SqlParameter(User.USERNAME, userName));
            LoadUserInfo(user);
            return user;
        }
        public bool HasUser(string userName)
        {
            string sql = string.Format(@"SELECT {1} FROM {0} WHERE {2}=@{2}", User.TABLENAME, User.USERID, User.USERNAME);
            return base.ExecuteList<User>(sql, new SqlParameter(User.USERNAME, userName)).Count > 0;
        }
        /// <summary>
        /// 获取父级用户的分成.
        /// </summary>
        /// <param name="userId">The parent id.</param>
        /// <returns></returns>
        public double GetParentShareRate(int userId)
        {
            string sql = string.Format(@"SELECT
(
	SELECT BU.SHARERATE FROM TB_USER B
	JOIN TB_USERINFO BU ON BU.USERID=B.USERID
	WHERE B.USERID=A.PARENTID
) -AU.SHARERATE
FROM TB_USER A
JOIN TB_USERINFO AU ON AU.USERID=A.USERID
WHERE A.{0}=@{0}", User.USERID);
            object rate = base.ExecuteScalar(sql, new SqlParameter(User.USERID, userId));
            return Convert.ToDouble(rate);
        }
        public IList<User> ListUserByCondition(string name, string userName, UserState state, SystemRole role, string sortField)
        {
            string sql = string.Format(@";WITH CTE AS({0})", _JoinUserInfoWithPageSql);
            List<SqlParameter> paramList = new List<SqlParameter>();
            StringBuilder condition = new StringBuilder();
            Action appendAdd = () =>
            {
                if (condition.Length > 0)
                    condition.Append(" and ");
            };
            if (!string.IsNullOrEmpty(name))
            {
                condition.AppendFormat(@"{0}=@{0}", UserInfo.NAME);
                paramList.Add(new SqlParameter(UserInfo.NAME, name));
            }
            if (!string.IsNullOrEmpty(userName))
            {
                appendAdd();
                condition.AppendFormat(@"{0}=@{0}", User.USERNAME);
                paramList.Add(new SqlParameter(User.USERNAME, userName));
            }
            appendAdd();
            condition.AppendFormat(@"{0}=@{0}", UserInfo.STATE);
            paramList.Add(new SqlParameter(UserInfo.STATE, (int)state));

            appendAdd();
            condition.AppendFormat(@"{0}=@{0}", User.ROLEID);
            paramList.Add(new SqlParameter(User.ROLEID, (int)role));

            if (!string.IsNullOrEmpty(sortField))
            {
                condition.AppendFormat(@" Order by {0} desc", sortField);
            }
            string subSql = string.Format(@"{0}SELECT * from CTE WHERE {1}", sql, condition.ToString());
            return base.ExecuteList<User>(subSql, paramList.ToArray());
        }

        private void LoadUserInfo(User user)
        {
            if (user == null) return;

            var userInfo = base.ToEnties<UserInfo>(base.DataTable)[0];  //若User不为空，UserInfo必不为空；
            user.UserInfo = userInfo;
        }
    }
}
